cd "Directory/Replication files"
use compustat19742004_externalfinance.dta, clear //from compustat
destring sic, replace
destring gvkey, replace
drop if sic==.
drop if gvkey==.
rename fyear year
format datadate %d
sort year
gen sic3=int(sic/10)
sum sic3
replace fopt=0 if fopt==.
replace invch=0 if invch==.
replace recch=0 if recch==.
replace apch=0 if apch==.
replace ibc=0 if ibc==.
replace dpc=0 if dpc==.
replace txdc=0 if txdc==.
replace esubc=0 if esubc==.
replace sppiv=0 if sppiv==.
replace fopo=0 if fopo==.
replace capx=0 if capx==.
by gvkey, sort: egen scf1=max(scf)
replace scf=scf1 if scf==.
sum gvkey if scf==.

gen changes=fopt+invch+recch+apch if scf==1 | scf==2 | scf==3
replace changes=ibc+dpc+txdc+esubc+sppiv+fopo if scf==7
sum changes
keep if year>1973 & year<2003

by gvkey, sort: egen changes_firm=sum(changes)
by gvkey, sort: egen capx_firm=sum(capx)
gen ratio=(capx_firm-changes_firm)/capx_firm
sum ratio
contract gvkey sic3 ratio
drop _freq
drop if ratio==.
by sic3, sort: egen external=median(ratio)
sum external, d
contract sic3 external
drop _freq
save ExternalFinance.dta, replace
